﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRest_IsAllowed]
#-- Purpose:		Determines if the user logged in has permissions to restore
#--					the database (via ORIGINAL_USER)
#--	Last Update:	11/25/2015
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRest_IsAllowed]
(	
	@database_name		varchar(255),
	@is_allowed			bit				OUTPUT
)
AS 

SET NOCOUNT ON

--- If the database is not online
DECLARE @is_restoring bit, @local_domain nvarchar(512), @agent_account nvarchar(512), @sql_account nvarchar(512)
SELECT	@is_restoring = ISNULL( [dbo].[fnRest_IsDatabaseRestoring] ( @database_name ), 0)
SELECT	@local_domain = [cfg_local_domain] FROM [dbo].[tblMaint_Config]

IF @is_restoring = 1
  BEGIN
	SELECT	@is_allowed = 1
  END
ELSE
  BEGIN
	--- Declare Local Variables
	DECLARE @db_owners TABLE ( name sysname )
	DECLARE @win_users TABLE ( account_name sysname, type char(8), privilege char(9), mapped_login_name sysname, permission_path sysname NULL )
	DECLARE	@has_rights bit, @reason sysname, @sSQL varchar(MAX), @group_name sysname, @user_name sysname
	SELECT	@has_rights = 0, @reason = 'No rights', @user_name = ORIGINAL_LOGIN(),
			@sSQL = 'USE ' + QUOTENAME(@database_name) + '; 
					SELECT	p.name
					FROM	sys.database_principals p
					JOIN	sys.database_role_members rm ON p.principal_id = rm.member_principal_id
					JOIN	sys.database_principals r ON r.principal_id = rm.role_principal_id
					WHERE	r.name = ''db_owner''

					UNION

					SELECT		(CASE WHEN owners.type = ''R'' THEN ISNULL(r.name, owners.name) ELSE owners.name END) name
					FROM		(
								SELECT	p.principal_id,
										p.name,
										p.type,
										p.type_desc
								FROM	sys.database_principals p
								JOIN	sys.database_role_members rm ON p.principal_id = rm.member_principal_id
								JOIN	sys.database_principals r ON r.principal_id = rm.role_principal_id
								WHERE	r.name = ''db_owner''
										AND p.name <> ''dbo''
								) owners
					LEFT JOIN	sys.database_role_members rm ON owners.principal_id = rm.role_principal_id
					LEFT JOIN	sys.database_principals r ON r.principal_id = rm.member_principal_id
					WHERE		(CASE WHEN owners.type = ''R'' THEN ISNULL(r.name, owners.name) ELSE owners.name END) IS NOT NULL
					'

	--- Get the service accounts
	EXEC	[master].[dbo].[xp_instance_regread]
				'HKEY_LOCAL_MACHINE',
				'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',
				'ObjectName',
				@agent_account OUTPUT

	EXEC	[master].[dbo].[xp_instance_regread]
				'HKEY_LOCAL_MACHINE',
				'SYSTEM\CurrentControlSet\services\MSSQLSERVER',
				'ObjectName',
				@sql_account OUTPUT

	--- If DOMAIN\ and @email.dom are passed in, remove @email.dom
	IF @user_name LIKE '%\%' AND @user_name LIKE '%@%'
		IF CHARINDEX('\', @user_name) < CHARINDEX('@', @user_name)
			SET	@user_name = SUBSTRING(@user_name, 1, CHARINDEX('@', @user_name) - 1)

	--- Get databases db_owner's
	IF EXISTS ( SELECT TOP 1 * FROM [master].[sys].[databases] WHERE name = @database_name )
	  BEGIN
		INSERT INTO @db_owners
			EXEC (@sSQL)
	  END
		  
	--- Get the user's memberships in domain groups
	IF EXISTS	(
				SELECT	TOP 1 * 
				FROM	[sys].[server_principals]
				WHERE	name = @user_name
						AND type IN ( 'U', 'G' )
				)
		OR NOT EXISTS (
				SELECT	TOP 1 * 
				FROM	[sys].[server_principals]
				WHERE	name = @user_name
				)
	  BEGIN
		IF @local_domain IS NULL OR @user_name LIKE (@local_domain + '%')
			INSERT INTO @win_users
				EXEC	[master].[dbo].[xp_logininfo] @user_name, 'all'

		--- Get rights if the user is the SQL Agent Account
		IF @user_name = @agent_account
		  BEGIN
			INSERT INTO @win_users
				EXEC	[master].[dbo].[xp_logininfo] 'NT SERVICE\SQLSERVERAGENT', 'all'

			UPDATE	@win_users
			SET		[account_name] = @agent_account
			WHERE	[account_name] = 'NT SERVICE\SQLSERVERAGENT'

			UPDATE	@win_users
			SET		[mapped_login_name] = @agent_account
			WHERE	[mapped_login_name] = 'NT SERVICE\SQLSERVERAGENT'
		  END

		--- Get rights if the user is the SQL Engine Account
		IF @user_name = @sql_account
		  BEGIN
			INSERT INTO @win_users
				EXEC	[master].[dbo].[xp_logininfo] 'NT SERVICE\MSSQLSERVER', 'all'

			UPDATE	@win_users
			SET		[account_name] = @sql_account
			WHERE	[account_name] = 'NT SERVICE\MSSQLSERVER'

			UPDATE	@win_users
			SET		[mapped_login_name] = @sql_account
			WHERE	[mapped_login_name] = 'NT SERVICE\MSSQLSERVER'
		  END
	  END
		  
	--- Is sysadmin?
	IF	EXISTS ( SELECT TOP 1 * FROM @win_users WHERE privilege = 'admin' AND mapped_login_name = @user_name )
			OR IS_SRVROLEMEMBER( 'sysadmin', @user_name ) = 1 
		SELECT	@has_rights = 1, @reason = 'sysadmin'

	--- Is dbcreator (for a new database)?
	ELSE IF NOT EXISTS ( SELECT TOP 1 * FROM [master].[sys].[databases] WHERE name = @database_name )
			AND IS_SRVROLEMEMBER( 'dbcreator', @user_name ) = 1
		SELECT	@has_rights = 1, @reason = 'dbcreator / new database'
		  
	--- Is db_owner (for existing database)?
	ELSE IF EXISTS	(
					SELECT	TOP 1 p.* 
					FROM	@db_owners p
					WHERE	p.name = @user_name
					)
			OR EXISTS (
					SELECT	TOP 1 p.name
					FROM	@db_owners p
					JOIN	@win_users w ON p.name = w.permission_path
							AND w.mapped_login_name = @user_name
					)
		SELECT	@has_rights = 1, @reason = 'db_owner / existing database'
		
	ELSE
	  BEGIN
		--- Is dbcreator through a group membership (for a new database)?
		IF NOT EXISTS ( SELECT TOP 1 * FROM [master].[sys].[databases] WHERE name = @database_name )
		  BEGIN
			DECLARE crs CURSOR FOR SELECT permission_path FROM @win_users GROUP BY permission_path
			OPEN crs
			FETCH NEXT FROM crs INTO @group_name
			WHILE @@FETCH_STATUS <> -1 AND @has_rights = 0
			  BEGIN
				SELECT	@has_rights = IS_SRVROLEMEMBER( 'dbcreator', @group_name ),
						@reason = 'dbcreator / new database'
				FETCH NEXT FROM crs INTO @group_name
			  END
			CLOSE crs
			DEALLOCATE crs
		  END
	  END

	--- Return a resultset of the response
	SELECT	@is_allowed = @has_rights
  END
  
SET NOCOUNT OFF
